package workforce.limousine.service;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.TransportTariff;

/**
 *
 * @author Tailer
 */
public class TransportTariffService implements Service {

    private DataSource ds = null;
    private int limit = 20;

    @Override
    public int getLimit() {
        return limit;
    }

    @Override
    public List search(int offset, Object[] certirial) {
        List list = new ArrayList();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("IFNULL(TABLE_ID, '-') AS TABLE_ID,");
        sql.append("IFNULL(TRANSPORTARIFF_ID, '-') AS TRANSPORTARIFF_ID,");
        sql.append("IFNULL(TARIFF_ORDER, 0) AS TARIFF_ORDER,");
        sql.append("IFNULL(DISTINATION, '-') AS DISTINATION,");
        sql.append("IFNULL(AREA_TYPE, 0) AS AREA_TYPE,");
        sql.append("IFNULL(AREA_COVER, 0) AS AREA_COVER,");
        sql.append("IFNULL(TIME_USAGE, '-') AS TIME_USAGE,");
        sql.append("IFNULL(POSTAL_CODE_COVER, '-') AS POSTAL_CODE_COVER,");
        sql.append("IFNULL(RATE_SEDAN,0) AS RATE_SEDAN,");
        sql.append("IFNULL(RATE_VAN,0) AS RATE_VAN,");
        sql.append("IFNULL(CREATE_BY, '-') AS CREATE_BY,");
        sql.append("IFNULL(CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        sql.append("IFNULL(UPDATE_BY, '-') AS UPDATE_BY,");
        sql.append("IFNULL(UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
        sql.append("(SELECT IFNULL(B.LOOKUP_NAME, '-') FROM lookup B WHERE B.LOOKUP_VALUE = A.AREA_TYPE AND B.LOOKUP_GROUP_ID = 'AREA_TYPE' ) AS AREA_TYPE_NAME ");
        sql.append("FROM transport_tariff A");
        if (certirial.length > 0) {
            sql.append(" where ");
        }
        if (certirial.length > 0) {
            sql.append(" A.DISTINATION like ?");
        }
        /*
        if (certirial.length > 1) {
            sql.append(" AND AREA_TYPE = ?");
        }
        if (certirial.length > 2) {
            sql.append(" AND POSTAL_CODE_COVER like ?");
        }*/
        sql.append(" LIMIT ? OFFSET ?");
        TransportTariff o = null;
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
           String name = (String) certirial[0];
            name = (name != null) ? name.replace('*', '%') : "";
            if (name.indexOf("%") == -1) {
                name = "%" + name + "%";
            }
            stmt.setString(1, name);
            stmt.setInt(2, limit);
            stmt.setInt(3, offset);
            //}
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                o = new TransportTariff();
                o.setTableId(rs.getString("TABLE_ID"));
                o.setTransportTariffId(rs.getString("TRANSPORTARIFF_ID"));
                o.setTariffOrder(rs.getInt("TARIFF_ORDER"));
                o.setDistination(rs.getString("DISTINATION"));
                o.setAreaType(rs.getInt("AREA_TYPE"));
                o.setAreaCover(rs.getString("AREA_COVER"));
                o.setTimeUsage(rs.getString("TIME_USAGE"));
                o.setPostalCodeCover(rs.getString("POSTAL_CODE_COVER"));
                o.setRateSedan(rs.getInt("RATE_SEDAN"));
                o.setRateVan(rs.getInt("RATE_VAN"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
                o.setAreaTypeName(rs.getString("AREA_TYPE_NAME"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int searchCount(int offset, Object[] certirial) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    @Override
    public TransportTariff findById(String tableId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("IFNULL(TABLE_ID, '-') AS TABLE_ID,");
        sql.append("IFNULL(TRANSPORTARIFF_ID, '-') AS TRANSPORTARIFF_ID,");
        sql.append("IFNULL(TARIFF_ORDER, 0) AS TARIFF_ORDER,");
        sql.append("IFNULL(DISTINATION, '-') AS DISTINATION,");
        sql.append("IFNULL(AREA_TYPE, 0) AS AREA_TYPE,");
        sql.append("IFNULL(AREA_COVER, 0) AS AREA_COVER,");
        sql.append("IFNULL(TIME_USAGE, '-') AS TIME_USAGE,");
        sql.append("IFNULL(POSTAL_CODE_COVER, '-') AS POSTAL_CODE_COVER,");
        sql.append("IFNULL(RATE_SEDAN,0) AS RATE_SEDAN,");
        sql.append("IFNULL(RATE_VAN,0) AS RATE_VAN,");
        sql.append("IFNULL(CREATE_BY, '-') AS CREATE_BY,");
        sql.append("IFNULL(CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        sql.append("IFNULL(UPDATE_BY, '-') AS UPDATE_BY,");
        sql.append("IFNULL(UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE ");
        sql.append("FROM transport_tariff where TABLE_ID = '");
        sql.append(tableId.trim());
        sql.append("'");
        TransportTariff o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new TransportTariff();
                o.setTableId(rs.getString("TABLE_ID"));
                o.setTransportTariffId(rs.getString("TRANSPORTARIFF_ID"));
                o.setTariffOrder(rs.getInt("TARIFF_ORDER"));
                o.setDistination(rs.getString("DISTINATION"));
                o.setAreaType(rs.getInt("AREA_TYPE"));
                o.setAreaCover(rs.getString("AREA_COVER"));
                o.setTimeUsage(rs.getString("TIME_USAGE"));
                o.setPostalCodeCover(rs.getString("POSTAL_CODE_COVER"));
                o.setRateSedan(rs.getInt("RATE_SEDAN"));
                o.setRateVan(rs.getInt("RATE_VAN"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(rs.getTimestamp("CREATE_DATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    @Override
    public boolean save(Object obj) {
        TransportTariff o = (TransportTariff) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO transport_tariff(TABLE_ID,TRANSPORTARIFF_ID,TARIFF_ORDER,DISTINATION,AREA_TYPE,AREA_COVER, ");
        sql.append("TIME_USAGE,POSTAL_CODE_COVER,RATE_SEDAN,RATE_VAN,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE ) ");
        sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?,CURRENT_TIMESTAMP)");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getTableId());
            stmt.setString(2, o.getTransportTariffId());
            stmt.setInt(3, o.getTariffOrder());
            stmt.setString(4, o.getDistination());
            stmt.setInt(5, o.getAreaType());
            stmt.setString(6, o.getAreaCover());
            stmt.setString(7, o.getTimeUsage());
            stmt.setString(8, o.getPostalCodeCover());
            stmt.setInt(9, o.getRateSedan());
            stmt.setInt(10, o.getRateVan());
            stmt.setString(11, o.getCreateBy());
            stmt.setString(12, o.getUpdateBy());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return true;
    }

    @Override
    public boolean update(Object obj) {
        TransportTariff o = (TransportTariff) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE transport_tariff ");
        sql.append("SET TRANSPORTARIFF_ID=?,TARIFF_ORDER=?,DISTINATION=?,AREA_TYPE=?,AREA_COVER=?,");
        sql.append("TIME_USAGE=?,POSTAL_CODE_COVER=?,RATE_SEDAN=?,RATE_VAN=?,UPDATE_BY=?,UPDATE_DATE=CURRENT_TIMESTAMP ");
        sql.append("where TABLE_ID='");
        sql.append(o.getTableId());
        sql.append("'");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getTransportTariffId());
            stmt.setInt(2, o.getTariffOrder());
            stmt.setString(3, o.getDistination());
            stmt.setInt(4, o.getAreaType());
            stmt.setString(5, o.getAreaCover());
            stmt.setString(6, o.getTimeUsage());
            stmt.setString(7, o.getPostalCodeCover());
            stmt.setInt(8, o.getRateSedan());
            stmt.setInt(9, o.getRateVan());
            stmt.setString(10, o.getUpdateBy());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean remove(Object tableId) {
        String id = (String) tableId;
        StringBuilder sql = new StringBuilder();
        sql.append("delete from transport_tariff where TABLE_ID = '");
        sql.append(id.trim());
        sql.append("'");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean removeAll(String ids[]) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement("");
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public Object findById(String uid, String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
